import time
from typing import Dict, Any, List
import httpx
from tenacity import retry, stop_after_attempt, wait_exponential
from intelli_port.commons.config import load_settings
from intelli_port.data_layer.clients import mysql_execute_read, mysql_execute_write, redis_client
from intelli_port.commons.audit import audit_event, write_ops_log
from intelli_port.notifications.notifier import broadcast


def _upsert_gate(code: str) -> int | None:
    try:
        r = mysql_execute_read("SELECT id FROM gates WHERE code=%s", (code,), fetch="one").get("rows", [])
        if r and r[0] and r[0][0] is not None:
            return int(r[0][0])
        mysql_execute_write("INSERT INTO gates(code, terminal_id) VALUES(%s, %s)", (code, 1))
        r2 = mysql_execute_read("SELECT id FROM gates WHERE code=%s", (code,), fetch="one").get("rows", [])
        return int(r2[0][0]) if r2 and r2[0] and r2[0][0] is not None else None
    except Exception:
        return None


def _upsert_flight(f: Dict[str, Any]) -> bool:
    try:
        fn = str(f.get("flight_no") or "").strip()
        gate_code = str(f.get("gate") or "").strip()
        status = str(f.get("status") or "scheduled").strip()
        gate_id = _upsert_gate(gate_code) if gate_code else None
        r = mysql_execute_read("SELECT id, gate_id, status FROM flights WHERE flight_no=%s", (fn,), fetch="one").get("rows", [])
        if r and r[0] and r[0][0] is not None:
            fid = int(r[0][0])
            old_gate_id = r[0][1]
            old_status = str(r[0][2] or "")
            mysql_execute_write("UPDATE flights SET gate_id=%s, status=%s WHERE id=%s", (gate_id, status, fid))
            try:
                if gate_id and old_gate_id != gate_id:
                    mysql_execute_write("INSERT INTO flight_events(flight_id, event, detail, created_at) VALUES(%s,'gate_change',%s,NOW())", (fid, gate_code))
                    try:
                        rds = redis_client()
                        payload = {"flight_id": fid, "flight_no": fn, "event": "gate_change", "detail": gate_code, "ts": int(time.time()*1000)}
                        rds.publish("flight_events", str(payload))
                        try:
                            broadcast({"flight_no": fn, "gate": gate_code, "status": status, "event": "gate_change"})
                        except Exception:
                            pass
                    except Exception:
                        pass
                if status and old_status != status:
                    mysql_execute_write("INSERT INTO flight_events(flight_id, event, detail, created_at) VALUES(%s,'status',%s,NOW())", (fid, status))
                    try:
                        rds = redis_client()
                        payload = {"flight_id": fid, "flight_no": fn, "event": "status", "detail": status, "ts": int(time.time()*1000)}
                        rds.publish("flight_events", str(payload))
                        try:
                            broadcast({"flight_no": fn, "gate": gate_code, "status": status, "event": "status"})
                        except Exception:
                            pass
                    except Exception:
                        pass
            except Exception:
                pass
        else:
            mysql_execute_write(
                "INSERT INTO flights(flight_no, airline, gate_id, origin, destination, status, departure_time) VALUES(%s,%s,%s,%s,%s,%s,NOW())",
                (fn, f.get("airline") or "", gate_id, f.get("origin") or None, f.get("destination") or None, status),
            )
        return True
    except Exception:
        return False


@retry(reraise=False, stop=stop_after_attempt(3), wait=wait_exponential(multiplier=0.2, min=0.2, max=2.0))
def sync_fids() -> Dict[str, Any]:
    s = load_settings()
    updated = 0
    source = "local"
    flights: List[Dict[str, Any]] = []
    if getattr(s, "fids_base_url", ""):
        try:
            headers = {"Authorization": f"Bearer {s.fids_auth_token}"} if getattr(s, "fids_auth_token", "") else {}
            with httpx.Client(timeout=float(getattr(s, "fids_timeout_s", 2.0))) as client:
                r = client.get(s.fids_base_url.rstrip("/") + "/flights")
                if r.status_code == 200:
                    data = r.json()
                    flights = list(data or [])
                    source = "external"
        except Exception:
            flights = []
    if not flights:
        try:
            rows = mysql_execute_read("SELECT flight_no, status FROM flights ORDER BY id DESC LIMIT 10", fetch="all").get("rows", [])
            for r in rows:
                fn = r[0]
                st = str(r[1] or "scheduled")
                ns = "boarding" if st == "scheduled" else ("delayed" if st == "boarding" else "scheduled")
                flights.append({"flight_no": fn, "gate": "A12", "status": ns})
        except Exception:
            pass
    for f in flights:
        ok = _upsert_flight(f)
        if ok:
            updated += 1
            try:
                r = mysql_execute_read("SELECT id FROM flights WHERE flight_no=%s", (f.get("flight_no"),), fetch="one").get("rows", [])
                if r and r[0] and r[0][0] is not None:
                    fid = int(r[0][0])
                    mysql_execute_write("INSERT INTO flight_events(flight_id, event, detail, created_at) VALUES(%s,'status',%s,NOW())", (fid, f.get("status")))
                    try:
                        rds = redis_client()
                        payload = {"flight_id": fid, "flight_no": f.get("flight_no"), "event": "status", "detail": f.get("status"), "ts": int(time.time()*1000)}
                        rds.publish("flight_events", str(payload))
                        try:
                            broadcast({"flight_no": f.get("flight_no"), "gate": f.get("gate"), "status": f.get("status"), "event": "status"})
                        except Exception:
                            pass
                    except Exception:
                        pass
            except Exception:
                pass
    write_ops_log("INFO", "fids.sync", {"updated": updated, "source": source})
    audit_event("fids", "sync", "flights", {"updated": updated, "source": source}, result="success" if updated > 0 else "noop")
    return {"updated": updated, "source": source}